<cfcomponent extends="base">

<!---************************************SELECT QUERIES**************************************--->
    
    
    <!--- Get  --->
    <cffunction name="funcGetRosettas" returntype="query">
        <cfargument default="" name="EmployeeID" />
        
        <cfquery name="qryGetSCFKronos1" datasource="#Session.DataSourceRosetta#">
            Select * 
            from RosettaStone
            where Disabled = 0
        </cfquery>
        
        <cfreturn qryGetSCFKronos1 />
    </cffunction>
    <!--- Get  --->
    
    
    
    <!--- Get  --->
    <cffunction name="funcGetSCFKronos1" returntype="query">
        <cfargument default="" name="EmployeeID" />
        
        <cfquery name="qryGetSCFKronos1" datasource="#Session.DataSourceWFCDB#">
            Select * 
            from tPersons
            WHERE PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.EmployeeID#" />  
        </cfquery>
        
        <cfreturn qryGetSCFKronos1 />
    </cffunction>
    <!--- Get  --->    
    
    
    
    <!--- Get Access Request --->
    <cffunction name="funcGetAccessRequest" returntype="query">
        <cfargument default="" name="EmployeeID" />
        
        <cfquery name="qryGetAccessRequest" datasource="#Session.DataSource#">
            Select BadgeAccessLevelID 
            from Employee
            Where ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.EmployeeID#" />
        </cfquery>
        
        <cfreturn qryGetAccessRequest />
    </cffunction>
    <!--- Get Access Request --->
    
    
    
    
    <!--- Get WFCSuite object--->
    <cffunction name="funcGetWFCPersonObj" returntype="query">
        <cfargument default="" name="RosettaID" />
        <cfquery name="qryGetWFCPersonObj" datasource="#Session.DataSource#">
			SELECT *			
			FROM EPAR.[getUsersWithSCFKronos1](1,1,1)
            WHERE RosettaID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.RosettaID#" />
        </cfquery> 
        
        <cfreturn qryGetWFCPersonObj />
    </cffunction>
    <!--- Get WFCSuite object--->
    
    
    
    
    <!--- Get File types --->
    <cffunction name="funcGetFileTypes" returntype="query">
        <cfargument default="" name="CategoryID" />
        <cfquery name="qryGetFileTypes" datasource="#Session.DataSource#">
            <!--- scf_GetFiles --->
            Select 
                * 
            from 
                FileTypes
            Where 
                CategoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.CategoryID#" />
            Order by 
                Name
        </cfquery> 
        
        <cfreturn qryGetFileTypes />
    </cffunction>
    <!--- Get File types --->    
    
    
    
    
    <!--- Get Filepath --->
    <cffunction name="funcGetFiles" returntype="query">
        <cfargument default="" name="TermReqID" />
        <cfquery name="qryGetFiles" datasource="#Session.DataSource#">
            <!--- scf_GetFiles --->
	        Select 
                FL.*, Name 
	        From 
                FileLibrary FL Inner Join 
                FileTypes FT On TypeID = FT.ID
	        Where 
                TermReqID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.TermReqID#" />
	        Order by 
                FileName, DateLoaded
        </cfquery> 
        
        <cfreturn qryGetFiles />
    </cffunction>
    <!--- Get Filepath --->
    
    
    
    
    <!--- Get Filepath --->
    <cffunction name="funcGetFilePath" returntype="query">
        
        <cfquery name="qryGetFilePath" datasource="#Session.DataSource#">
            <!--- scf_GetFilePath --->
            Select 
                Directory 
            from 
                FileLibraryDirectory
        </cfquery> 
        
        <cfreturn qryGetFilePath />
    </cffunction>
    <!--- Get Filepath --->
    
    
    
    
    <!--- Get Termination Details --->
    <cffunction name="funcGetTermDetails" returntype="query">
        <cfargument default="-1" name="TermReqID" required="yes" />
        <cfargument default="-1" name="StepID" required="yes" />
        
        <cfquery name="qryGetTermDetails" datasource="#Session.DataSource#">
			Select 
			    R.RosettaID, R.EPAR_ID, R.UserAccount, R.AD_GUID, A.ID as ActionStepID, 
			    A.TermReqID, InitialTermReqByID, EmpStatusIDNo, OrgCodeIDNo, 
			    RehireElig, TermTypeID, EmpStatusReasonCode, TermDate, SysTermDate, COBRAEndOfMonth, CostCode, 
			    ActionByID, ActTimeStamp, Notes
			From 
			    TerminationRequests T Inner Join 
			    StepActions A on T.ID = A.TermReqID and StepID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.StepID#" /> Inner Join 
			    Rosetta.dbo.RosettaStone R on R.RosettaID = T.RosettaID 
			    Inner Join (
					Select TermReqID, ActionByID as InitialTermReqByID
					From StepActions 
					Where TermReqID is not Null and StepID = 18
			    ) X on X.TermReqID = A.TermReqID
			Where A.TermReqID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.TermReqID#" />
        </cfquery> 
        
        <cfreturn qryGetTermDetails />
    </cffunction>
    <!--- Get Termination Details --->
    
    
    
    <!--- Get Termination Details --->
    <cffunction name="funcGetAllTermDetails" returntype="query">
        <cfargument default="-1" name="TermReqID" required="yes" />
        
        <cfquery name="qryGetTermDetails" datasource="#Session.DataSource#">
            Select 
                R.RosettaID, R.EPAR_ID, R.UserAccount, R.AD_GUID, A.ID as ActionStepID, 
                A.TermReqID, InitialTermReqByID, EmpStatusIDNo, OrgCodeIDNo, 
                RehireElig, TermTypeID, EmpStatusReasonCode, TermDate, SysTermDate, COBRAEndOfMonth, CostCode 
                ActionByID, ActTimeStamp, Notes
            From 
                TerminationRequests T Inner Join 
                StepActions A on T.ID = A.TermReqID  Inner Join 
                Rosetta.RosettaStone R on R.RosettaID = T.RosettaID 
                Inner Join (
                    Select TermReqID, ActionByID as InitialTermReqByID
                    From StepActions 
                    Where TermReqID is not Null and StepID = 18
                ) X on X.TermReqID = A.TermReqID
            Where A.TermReqID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.TermReqID#" />
        </cfquery> 
        
        <cfreturn qryGetTermDetails />
    </cffunction>
    <!--- Get Termination Details --->
    
    
    
    
    <!--- Get RosettaID and Last name --->
    <cffunction name="funcGetAllEmployees" returntype="query">
   
		<cfquery name="qryGetAllEmployees" datasource="SCF_General">
			Select RosettaID, LastNameFirst 
			From func_GetUsers(0,0,0)
			Order By LastNameFirst
		</cfquery> 
        
        <cfreturn qryGetAllEmployees />
    </cffunction>
    <!--- Get RosettaID and Last name --->
    
    
    
    
    <!--- Get Emp Info by HRProd_PersonIDNo from SCFVM18 WFCSuite --->
    <cffunction name="funcGettPersonsObj" returntype="query">
        <cfargument default="-1" name="PersonIDNo" required="yes" />
        
        <cfquery name="qryGettPersonsObj" datasource="#Session.DataSourceWFCDB#">
            Select 
                *
            From 
                tPersons 
            Where
                PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" 
                                value="#ARGUMENTS.PersonIDNo#" />
        </cfquery> 
        
        <cfreturn qryGettPersonsObj />
    </cffunction>
    <!--- Get Emp Info by HRProd_PersonIDNo from SCFVM18 WFCSuite --->
    
    
    
    
    
    <!--- Get Emp Info by RosettaID from Rosetta Table --->
    <cffunction name="funcGetRosettaObj" returntype="query">
        <cfargument default="-1" name="RosettaID" required="yes" />
        
        <cfquery name="qryGetRosettaObj" datasource="#Session.DataSourceRosetta#">
            Select 
				RosettaID, UserAccount, AD_GUID, NonSCFMasterID, WFC_PersonID, 
                Fundware_EmpNo, IVIS_IDX, ServiceDeskID, HRProd_PersonIDNo, 
                ProcureITID, AccessControl_ID, EPAR_ID, Disabled
            From 
                RosettaStone 
            Where
                RosettaID = <cfqueryparam cfsqltype="cf_sql_integer" 
                                value="#ARGUMENTS.RosettaID#" />
        </cfquery> 
        
        <cfreturn qryGetRosettaObj />
    </cffunction>
    <!--- Get Emp Info by RosettaID from Rosetta Table --->
    
    
    
    
    <!--- Get Term Types--->
    <cffunction name="funcGetPersonCodes" returntype="query">
        <cfargument default="" name="CodeType" />
        
        <cfquery name="qryGetPersonCodes" datasource="#Session.DataSourceWFCDB#">
        	<!---scf_SCFVM18_GetPersonCodes 'TT' --->
            Select 
            	PersonCodeIDNo, PersonCode 
            From 
            	tSY_Person_Codes 
            Where
            	1 = 1
                <cfif ARGUMENTS.CodeType NEQ "">
            		AND PersonCodeType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.CodeType#" />
                </cfif>
        </cfquery> 
        
    	<cfreturn qryGetPersonCodes />
    </cffunction>
    <!--- Get Term Types--->
    
    
    
    
    <!--- Get NonSCF types--->
    <cffunction name="funcGetNonSCFTypes" returntype="query">
    	<cfargument default="1" name="ExcludeANMC" />
        <cfargument default="" name="TypeID" />
        
        <cfquery name="qryGetNonSCFTypes" datasource="#Session.DataSourceRosetta#">
        	<!---scf_GetNonSCFTypes 0, #ARGUMENTS.NonSCFTypeID#--->            
            Select 
            	* 
            from 
            	NonSCFTypes
            Where
            	1=1
            	<cfif ARGUMENTS.ExcludeANMC EQ 1>
                	AND ID <> 1
                </cfif>
                <cfif ARGUMENTS.TypeID NEQ "">
                	AND ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.TypeID#" />
                </cfif>
                
        </cfquery> 
        
    	<cfreturn qryGetNonSCFTypes />
    </cffunction>
    <!--- Get NonSCF types--->
    
    
    
    
    <!--- Get Step Comments --->
    <cffunction name="funcGetCommentsByStepType" returntype="query">
    	<cfargument default="-1" name="EmployeeID" />
        <cfargument default="-1" name="StepID" />
        
        <cfquery name="qryGetCommentsByStepType" datasource="#Session.DataSource#">
        	Select 
            	Notes 
            From 
            	StepActions
			Where 
            	EmployeeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.EmployeeID#" /> AND
                StepID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.StepID#" />
        </cfquery> 
        
    	<cfreturn qryGetCommentsByStepType />
    </cffunction>
    <!--- Get Step Comments --->
    
    
    
    
    <!--- Get GetNextMailServer --->
    <cffunction name="funcGetNextMailServer" returntype="query">
        
        <cfquery name="qryGetNextMailServer" datasource="#Session.DataSource#">
        	<!---scf_GetNextServer - scott's old stored procedure--->
            Select 
            	ServerID, ServerName 
            From 
            	NextMailServer N Inner Join 
            	MailServers M On N.ServerID = M.MailServerID
        </cfquery> 
        
    	<cfreturn qryGetNextMailServer />
    </cffunction>
    <!--- Get GetNextMailServer --->
    
    
    
    
    <!--- Get PAY_RATE_FREQUENCY_CODES --->
    <cffunction name="funcGetAF" returntype="query">
    	<cfargument default="-1" name="FrequencyID" />
        
        <cfquery name="qryGetAF" datasource="#Session.DataSourceWFCDB#">
            Select 
            	AnnualizingFactor
            From 
            	tSY_PAY_RATE_FREQUENCY_CODES
            Where 
            	PayRateFrequencyCodeDomainIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.FrequencyID#" />
        </cfquery> 
        
    	<cfreturn qryGetAF />
    </cffunction>
    <!--- Get PAY_RATE_FREQUENCY_CODES --->
    
    
    
    
	<!--- Get Access --->
    <cffunction name="funcGetAccess" returntype="query">
        <cfargument default="-1" name="AccessLevelID"  />
        
        <cfquery name="qryGetAccess" datasource="#Session.DataSourceAccessControl#">
            Select AccessLvID, Descript
            From AccessLvl
            <cfif ARGUMENTS.AccessLevelID NEQ -1>
                Where AccessLVID in (#ARGUMENTS.AccessLevelID#)
            </cfif>
            Order by Descript
        </cfquery>
        
    	<cfreturn qryGetAccess />
    </cffunction>
    <!--- Get Access --->
    
    
    
    
    <!--- Get Access --->
    <cffunction name="funcGetAccessID" returntype="query">
        <cfargument default="-1" name="Badge"  />
        
        <cfquery name="qryGetAccessID" datasource="AccessControl">
            Select EmpID
			From Badge
			Where ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.Badge#" />
        </cfquery>
        
        <cfreturn qryGetAccessID />
    </cffunction>
    <!--- Get Access --->
    
	
	
	
	<!--- Get Depts --->
    <cffunction name="funcGetDepts" returntype="query">
    	<cfargument default="-1" name="ID" type="any" />
        
        <cfquery name="qryGetDepts" datasource="#Session.DataSourceWFCDB#"  cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select 
            	OrganizationDescription + ' (' + rtrim(OrganizationCode) + ')' as Org, OrgCodeIDNo, OrganizationDescription
            From 
            	tOrganizations
            Where 
            	OrganizationToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value="1/1/3000" />
                <cfif ARGUMENTS.ID NEQ -1>
                	AND OrgCodeIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.ID#" />
                </cfif>
            Order by 
            	OrganizationDescription, OrganizationCode		
        </cfquery>  
        
    	<cfreturn qryGetDepts />
    </cffunction>
    <!--- Get Depts --->
     
	
	
	
	<!--- Get SupDept --->
    <cffunction name="funcGetSupDept" returntype="query">
    	<cfargument default="" name="PersonIDNo" type="string" />
        
        <cfquery name="qryGetSupDept" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select 
            	OrgCodeIDNo
            From 
            	tPosition_Orgs O Inner Join 
                tPerson_Positions P On P.PositionIDNo = O.PositionIDNo
            Where 
            	PersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.PersonIDNo#" /> AND 
           		PositionToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value="1/1/3000" />
        </cfquery> 
        
    	<cfreturn qryGetSupDept />
    </cffunction>
    <!--- Get SupDept --->
     
	
	
	
	<!--- Get Container --->
    <cffunction name="funcGetContainer" returntype="query">
    	<cfargument default="-1" name="OUID"  />
        
        <cfquery name="qryGetContainer" datasource="#Session.DataSource#">
        	<!---scf_GetContainers - old scott stored procedure--->
            Select [OUID], [Container], [HomeDir]
            From ADContainer
            WHERE 
            	1=1 
                <cfif ARGUMENTS.OUID NEQ -1>
                	AND OUID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.OUID#" />
                </cfif>
            Order by Container
        </cfquery>
        
    	<cfreturn qryGetContainer />
    </cffunction>
    <!--- Get Container --->
     
	
	
	
	<!--- Get States --->
    <cffunction name="funcGetStates" returntype="query">
    
        <cfquery name="qryGetStates" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select LocalCodeIDNo, LocalCodeAbbreviation
            From tLocal_Codes
            Where LocalCodeType = <cfqueryparam cfsqltype="cf_sql_varchar" value="ST" />
            Order by LocalCodeAbbreviation
        </cfquery>
        
        <cfreturn qryGetStates />
    </cffunction>
    <!--- Get States --->
     
	
	
	
	<!--- Get Phone types --->
    <cffunction name="funcGetPhoneTypes" returntype="query">
    
        <cfquery name="qryGetPhoneTypes" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select 
                TypeCodeIdNo, TypeCode
            From 
                tTYPE_CODES
            Where 
                TypeCodeToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value="1/1/3000" /> AND 
                TypeCodeType = <cfqueryparam cfsqltype='cf_sql_varchar' value='PT' /> AND 
                TypeCodeIdNo <> <cfqueryparam cfsqltype="cf_sql_integer" value='23' />
            Order By 
                TypeCodeIdNo
        </cfquery>
    
            <cfreturn qryGetPhoneTypes />
    </cffunction>
    <!--- Get Phone types --->
    
    
    
    <!--- Get CompTypes --->
    <cffunction name="funcGetCompTypes" returntype="query">
    	<cfargument default="-1" name="CompType" required="no" />
        
            <cfquery name="qryGetCompTypes" datasource="#Session.DataSourceWFCDB#"> 
                Select 
                	TypeCode, TypeCodeIDNo
                From 
                	tType_Codes
                Where 
                	TypeCodeToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value='1/1/3000' /> and 
                	TypeCodeType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.CompType#" />
            </cfquery>
        
    	<cfreturn qryGetCompTypes />
    </cffunction>
    <!--- Get CompTypes --->
     
	
	
	
	<!--- Get Lunch types --->
    <cffunction name="funcGetLunch" returntype="query">
    
        <cfquery name="qryGetLunch" datasource="#Session.DataSource#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            <!---scf_GetLunchTypes - old scott stored procedure--->
            SELECT 
            	ID, LunchType
            FROM 
            	Lunch
        </cfquery>
        
    	<cfreturn qryGetLunch />
    </cffunction>
	<!--- Get Lunch types --->
     
	
	
	
	<!--- Get Job Postings--->
    <cffunction name="funcGetJobPostings" returntype="query">
    	<cfargument default="" name="PostingID" type="string" />
        
        <cfquery name="qryGetJobPostings" datasource="JustinsJobProg" >
            Select ID, Bulletin, JobTitle
            From joblistings
            <cfif ARGUMENTS.PostingID NEQ "">
            	Where ID = <cfqueryparam cfsqltype="cf_sql_integer" value='#ARGUMENTS.PostingID#' />
            </cfif>
            Order By Jobtitle, Bulletin
        </cfquery>
        
    	<cfreturn qryGetJobPostings />
    </cffunction>
    <!--- Get Job Postings--->
    
    
    
    
    <!--- Get Position Info --->
    <cffunction name="funcGetPositionInfo" returntype="query">
        <cfargument default="-1" name="PositionIdNo" type="numeric" required="no" />
        
        <cfquery name="qryGetPositionInfo" datasource="#Session.DataSourceWFCDB#">
	        Select 
                C.PositionIDNo, PositionCode, PositionCodeDescription
	        From 
                tPosition_Codes C Inner Join (
	                Select 
                        PositionIDNo, Max(PositionToEffectDate) as LastFilled
	                From
                        tPerson_Positions
	                Group by
                        PositionIDNo
	            ) X On X.PositionIDNo = C.PositionIDNo 
	        Where 
                C.PositionIdNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.PositionIdNo#" />
        </cfquery>
        
        <cfreturn qryGetPositionInfo />
    </cffunction>
    <!--- Get Position Info ---> 
    
    
	
	
    <!--- Get Position Codes --->
    <cffunction name="funcGetPositionCodes" returntype="query">
    	<cfargument default="-1" name="PositionID" type="numeric" required="no" />
        
        <cfquery name="qryGetPositionCodes" datasource="#Session.DataSourceWFCDB#">
        	Select 
            	PositionIDNo, PositionCode, PositionCodeDescription
            From 
            	tPosition_Codes 
            Where 
            	PositionIdNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.PositionID#" />
        </cfquery>
        
    	<cfreturn qryGetPositionCodes />
    </cffunction>
    <!--- Get Position Codes  --->
	
    
    
    
    <!--- Get Salary Hourly --->
    <cffunction name="funcGetSalaryHourly" returntype="query">
    	<cfargument default="-1" name="SalaryHourlyType" required="no" />
        
        <cfquery name="qryGetSalaryHourly" datasource="#Session.DataSourceWFCDB#">
            Select 
            	PersonCodeIDNo as ID, PersonCode
            From 
            	tSY_Person_Codes
            Where 
            	PersonCodeType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.SalaryHourlyType#" />
                
        </cfquery>
        
    	<cfreturn qryGetSalaryHourly />
    </cffunction>
    <!--- Get Salary Hourly --->
    
    
    
    
    <!--- Get Frequency --->
    <cffunction name="funcGetFrequency" returntype="query">
    	<cfargument default="-1" name="FrequencyID" type="numeric" required="no" />
        
        <cfquery name="qryGetFrequency" datasource="#Session.DataSourceWFCDB#">
            Select 
            	PayRateFrequencyCodeDomainIdNo as ID, PayRateFrequencyCode, AnnualizingFactor
            From 
            	tSY_Pay_Rate_Frequency_Codes
            Where 
            	<cfif ARGUMENTS.FrequencyID NEQ -1>
            		PayRateFrequencyCodeDomainIdNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.FrequencyID#" />
                <cfelse>
                	1=1
                </cfif>
        </cfquery>
        
    	<cfreturn qryGetFrequency />
    </cffunction>
    <!--- Get Frequency --->
    
    
    
    <!--- Get Workers CompInfo --->
    <cffunction name="funcGetWorkersCompInfo" returntype="query">
        
        <cfquery name="qryGetWorkersCompInfo" datasource="#Session.DataSourceWFCDB#">
            Select *
            From 
            	tWCMP_Workers_Compensation_Codes
            Where 
            	WorkersCompensationIDNo <> <cfqueryparam cfsqltype="cf_sql_integer" value="-10001" />
            Order by 
            	WorkersCompensationCode
        </cfquery>
        
    	<cfreturn qryGetWorkersCompInfo />
    </cffunction>
    <!--- Get Workers CompInfo --->
       
    
    
    
    <!--- Get Positions --->
    <cffunction name="funcGetPositions" returntype="query">
        <cfargument default="-1" name="PositionID" type="numeric" />
        
        <cfquery name="qryGetPositions" datasource="#Session.DataSourceWFCDB#">        
            Select distinct PositionIDNo, PositionCode, PositionCodeDescription
            From tPosition_Codes 
            <!--- Requested by HR, 4/18/2008, show all positions, not just open positions--->
            <!---Select C.PositionIDNo, PositionCode, PositionCodeDescription
            From tPosition_Codes C
                Inner Join (
                    Select PositionIDNo, Max(PositionToEffectDate) as LastFilled
                    From tPerson_Positions
                    Group by PositionIDNo
                    Having Max(PositionToEffectDate) < '1/1/3000') X On X.PositionIDNo = C.PositionIDNo	--->
            Where PositionCodeToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value='1/1/3000' />
            <Cfif ARGUMENTS.PositionID NEQ -1>
            	AND PositionIDNo  = <cfqueryparam cfsqltype="cf_sql_integer" value='#ARGUMENTS.PositionID#' />
            </Cfif>
            Order by PositionCode
        </cfquery>
        
    	<cfreturn qryGetPositions />
    </cffunction>
    <!--- Get Positions --->
    
    
    
    <!--- Get EmpCodes --->
    <cffunction name="funcGetEmpCodes" returntype="query">
        
        <cfquery name="qryGetEmpCodes" datasource="#Session.DataSourceWFCDB#">
            Select EmploymentCode, EmploymentCodeIDNo
            From tEmployment_Codes
            Where EmploymentCodeIDNo > <cfqueryparam cfsqltype="cf_sql_integer" value="0" />
            Order by EmploymentCodeIDNo
        </cfquery> 
        
    	<cfreturn qryGetEmpCodes />
    </cffunction>
    <!--- Get EmpCodes --->
        
       
            
    
	<!--- Get Employee Info --->
    <cffunction name="funcGetEmpInfo" returntype="query">
    	<cfargument default="-1" name="StepActionID" type="numeric" required="yes" />
        <cfargument default="0" name="ActTimeStamp" required="no" />
        
        <cfquery name="qryGetEmpInfo" datasource="#Session.DataSource#">
            <!---scf_GetEmpInfoByStep #URL.ID# - Scott's old Stored Procedure--->
            Select 
            	E.*, L.LunchType, S.ShiftType
            From 
            	Employee E Inner Join 
                StepActions A On A.EmployeeID = E.ID Left join 
                Lunch L On E.LunchID = L.ID Left join 
                Shift S On E.ShiftID = S.ID
            Where 
            	A.ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.StepActionID#" /> and 
                <cfif ARGUMENTS.ActTimeStamp EQ 0>
                	ActTimeStamp is Null
                <cfelse>
                	1=1
                </cfif>
        </cfquery>
        
    	<cfreturn qryGetEmpInfo />
    </cffunction>
    <!--- Get Employee Info --->
    
    
    
    <!--- Get Employee Info Termination--->
    <cffunction name="funcGetEmpInfoTermination" returntype="query">
        <cfargument default="-1" name="StepActionID" type="numeric" required="yes" />
        
        <cfquery name="qryGetEmpInfo" datasource="#Session.DataSource#">
            <!---scf_GetEmpInfoByStep #URL.ID# - Scott's old Stored Procedure--->
            Select 
                T.*
            From 
                StepActions A Inner Join 
                TerminationRequests T On A.TermReqID = T.ID
            Where 
                A.ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.StepActionID#" /> and 
                ActTimeStamp is Null
        </cfquery>
        
        <cfreturn qryGetEmpInfo />
    </cffunction>
    <!--- Get Employee Info Termination--->
    
    
    
    
    <!--- Get Shifts --->
    <cffunction name="funcGetShifts" returntype="query">
        
        <cfquery name="qryGetShifts" datasource="#Session.DataSource#">
        	<!---scf_GetShifts - Scott's old Stored Procedure--->
            Select * 
            from 
            	Shift	
        </cfquery>
        
    	<cfreturn qryGetShifts />
    </cffunction>
    <!--- Get Shifts --->
    
    
    
    
    <!--- Get Ethnic Codes --->
    <cffunction name="funcGetEthnicCodes" returntype="query">
        
        <cfquery name="qryGetEthnicCodes" datasource="#Session.DataSourceWFCDB#">
            Select 
            	EthnicCode, EthnicCodeIDNo
            From 
            	tEthnic_Codes
            Where 
            	EthnicCodeToEffectDate = '1/1/3000' and EthnicCodeIDNo <> -10001
            Order by 
            	EthnicCode		
        </cfquery>
        
    	<cfreturn qryGetEthnicCodes />
    </cffunction>
    <!--- Get Ethnic Codes --->
    
    
    
    
    <!--- Get Marital Codes --->
    <cffunction name="funcGetMaritalCodes" returntype="query">
        
        <cfquery name="qryGetMaritalCodes" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select PersonCode, PersonCodeIDNo
            From tSY_Person_Codes
            Where PersonCodeType = 'MS'
            Order by PersonCode
        </cfquery>
        
    	<cfreturn qryGetMaritalCodes />
    </cffunction>
    <!--- Get Marital Codes --->
    
    
    
    
    <!--- Get Gender Codes --->
    <cffunction name="funcGetGenderCodes" returntype="query">
        
        <cfquery name="qryGetGenderCodes" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select PersonCode, PersonCodeIDNo
            From tSY_Person_Codes
            Where PersonCodeType = 'GN' and PersonCodeIDNo <> -10153
            Order by PersonCode	
        </cfquery>
        
    	<cfreturn qryGetGenderCodes />
    </cffunction>
    <!--- Get Gender Codes --->
    
    
    
    
    <!--- Get Get I9 Codes --->
    <cffunction name="funcGetI9Codes" returntype="query">
        
        <cfquery name="qryGetI9Codes" datasource="#Session.DataSourceWFCDB#" cachedwithin="#CreateTimeSpan(0,9,0,0)#">
            Select PersonCode, PersonCodeIDNo
            From tSY_Person_Codes
            Where PersonCodeType = 'I9'
            Order by PersonCode	Desc
        </cfquery>
        
    	<cfreturn qryGetI9Codes />
    </cffunction>
    <!--- Get Get I9 Codes --->
    
    
    
    
    <!--- Get Reason Codes --->
    <cffunction name="funcGetReasonCodes" returntype="query">
    	<cfargument default="-1" name="ReasonCodeType" type="any" required="yes" />
        
        <cfquery name="qryGetReasonCodes" datasource="#Session.DataSourceWFCDB#">
            Select 
            	ReasonCode, ReasonCodeIDNo
            From 
            	tReason_Codes
            Where 
            	ReasonCodeToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value='1/1/3000' /> and 
            	ReasonCodeType = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.ReasonCodeType#" />
        </cfquery>
        
    	<cfreturn qryGetReasonCodes />
    </cffunction>
    <!--- Get Reason Codes --->
    
    
    
    <!--- Get PositionInfo --->
    <cffunction name="funcGetPositionCode" returntype="query">
		<cfargument name="PositionID" required="yes" />
        		
        <cfquery name="qryGetPositionCode" datasource="#Session.DataSourceWFCDB#">
            Select *
            From 
            	tJob_Codes J Inner Join 
                tPosition_Codes P on P.JobCodeIdNo = J.JobCodeIdNo Inner Join 
                tPay_Grade_Codes G on J.PayGradeIdNo = G.PayGradeIDNo
            Where 
            	P.PositionIdNo = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.PositionID#" />
		</cfquery>
        
		<cfreturn qryGetPositionCode />
	</cffunction>
	<!--- Get PositionInfo --->
    
    
    
    <!--- Get Relations --->
    <cffunction name="funcGetRelations" returntype="query">
        		
        <cfquery name="qryGetRelations" datasource="#Session.DataSourceWFCDB#">
            Select RelationCode, RelationCodeIDNo
            From tRelation_Codes
            Where RelationCodeToEffectDate = <cfqueryparam cfsqltype="cf_sql_date" value='1/1/3000' /> 
            Order by RelationCode
    	</cfquery>
        
		<cfreturn qryGetRelations />
	</cffunction>
	<!--- Get Relations --->
    
    
    
    
    <!--- Get Contacts --->
    <cffunction name="funcGetEmployeeContacts" returntype="query">
    	<cfargument name="EmployeeID" required="yes" />
    	<cfargument name="ContactType" required="yes" />
        		
        <cfquery name="qryGetEmployeeContacts" datasource="#Session.DataSource#"> 
           <!--- scf_GetEmpContactInfo #intEmpID#, 2 - Scott's old stored procedure--->
            Select 
            	ID, EmployeeID, First, Last, Email, Address1, Address2, City, State, ZIP, 
                HomePhone, CellPhone, WorkPhone, WorkPhoneExt, PhonePrimaryID, RelationID, ContactTypeID
            from 
            	Contacts
            Where 
            	EmployeeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.EmployeeID#" /> AND 
                ContactTypeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.ContactType#" />
            order by 
            	ID
        </cfquery>
            
		<cfreturn qryGetEmployeeContacts />
	</cffunction>
	<!--- Get Contacts --->
    
    

    
    
<!---************************************INSERT QUERIES**************************************--->




	<!--- Insert Step Actions --->
    <cffunction name="funcInsertAction" returntype="any"
        hint="Arguments: EmployeeId, StepID, ActionByID, Notes, ActTimeStamp, TermReqID">
    	<cfargument default="-1" name="EmployeeID" required="yes" />
        <cfargument default="-1" name="StepID" required="yes" />
        <cfargument default="" name="ActionByID" required="no" />
        <cfargument default="" name="Notes" required="no" />
        <cfargument default="" name="ActTimeStamp" required="no" />
        <cfargument default="" name="TermReqID" required="no" />
        
		
        <cfquery name="qryInsertAction" datasource="#Session.DataSource#">
        	<!--- scf_InsertAction #intEmpID#, 1   - Scott's old Stored Procedure--->                           
            INSERT INTO StepActions (
            	EmployeeID, 
                StepID,
                ActionByID
                
                <cfif ARGUMENTS.Notes NEQ "">
                	,Notes
               	</cfif>
                <cfif ARGUMENTS.ActTimeStamp NEQ "">
                	,ActTimeStamp
                </cfif>
                <cfif ARGUMENTS.TermReqID NEQ "">
                	,TermReqID
                </cfif>
            )
            VALUES (
            	<cfif ARGUMENTS.EmployeeID EQ "">              
                    <cfqueryparam cfsqltype='cf_sql_integer' null="Yes" />,
                <cfelse>
                    <cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.EmployeeID#' />,
                </cfif>
                
                <cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.StepID#' />,
                
                <cfif ARGUMENTS.ActionByID EQ "">              
                    <cfqueryparam cfsqltype='cf_sql_integer' null="Yes" />
                <cfelse>
                    <cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.ActionByID#' />
                </cfif>
                
                <cfif ARGUMENTS.Notes NEQ "">
                	,<cfqueryparam cfsqltype='cf_sql_varchar' value='#ARGUMENTS.Notes#' />
               	</cfif>
                <cfif ARGUMENTS.ActTimeStamp NEQ "">
                	,getdate()
                </cfif>
                <cfif ARGUMENTS.TermReqID NEQ "">
                	,<cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.TermReqID#' />
                </cfif>
                )  
                
            SELECT @@Identity AS NewStepID                  
        </cfquery>
		
        <cfreturn qryInsertAction />
    </cffunction>
    <!--- Insert Step Actions --->
    
    
    
    
    
    <!--- Insert Error Log --->
    <cffunction name="funcInsertErrorLog" returntype="any" >
    	<cfargument default="-1" name="StepID" required="yes" />
        <cfargument default="-1" name="UserRosettaID" required="yes" />
        <cfargument default="-1" name="Detail" required="yes" />
		
        <cfquery name="qryInsertErrorLog" datasource="#Session.DataSource#">
            <!---scf_InsertErrorLog #StepID#, '#Session.intUserRosettaID#', N'Error generating the Active Directory record: #CFCATCH.Detail#  Trace: #thread.Trace#  Result:#thread.Result# #thread.strADResult#'- scott's old stored procedure --->
        	Insert Into ErrorLog 
            	(StepID, RosettaID, ErrorDetail)
			Values 
            	(
                	<cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.StepID#' />, 
                    <cfqueryparam cfsqltype='cf_sql_integer' value='#ARGUMENTS.UserRosettaID#' />, 
                    <cfqueryparam cfsqltype="cf_sql_varchar" value='#ARGUMENTS.Detail#' />
                )    
        </cfquery>
                            
    </cffunction>
    <!--- Insert Error Log --->
    
        


<!---************************************UPDATE QUERIES**************************************--->
    
    
    <cffunction name="funcUpdateEmployee" returntype="void">
        <cfargument default="-1" name="EPARID" type="numeric" required="yes" />
        <cfargument default="" name="ADUserID" required="no" />
        <cfargument default="" name="ADGUID"  required="no" />
        <cfargument default="-111" name="HRPersonIDNo" required="no" />
        <cfargument default="-111" name="RosettaID" required="no" />
        
        <cfquery name="qryUpdateAction" datasource="#Session.DataSource#">
            Update 
                Employee
            Set
                1=1
                <cfif ARGUMENTS.ADUserID NEQ "">,ADUserID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.ADUserID#" /></cfif>             
                <cfif ARGUMENTS.ADGUID NEQ "">,ADGUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.ADGUID#" /></cfif>
                <cfif ARGUMENTS.HRPersonIDNo NEQ -111>,HRPersonIDNo = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.HRPersonIDNo#" /></cfif>
                <cfif ARGUMENTS.RosettaID NEQ -111>,RosettaID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.RosettaID#" /></cfif>
            Where 
                ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.EPARID#" />
            </cfquery>
    </cffunction>
    
    
    
    
	<!--- Step2 Update Action --->
    <cffunction name="funcUpdateAction" returntype="any">
    	<cfargument default="-1" name="StepID" type="numeric" required="yes" />
        <cfargument default="-1" name="ActionByID" type="numeric" required="yes" />
        <cfargument default="" name="Notes"  required="no" />
        
        <cfquery name="qryUpdateAction" datasource="#Session.DataSource#">
            <!---scf_UpdateAction #intStepID#, #Session.intUserRosettaID#, '#strNotes#' - Scott's old Stored Procedure--->
            Update 
                StepActions
            Set 
                ActionByID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.ActionByID#" />, 
                ActTimeStamp = getDate() 
                <cfif ARGUMENTS.Notes neq "" >
                	,Notes = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.Notes#" />
                </cfif>
            Where 
                ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.StepID#" />
            </cfquery>
        
    	<cfreturn True />
    </cffunction>
    <!--- Step2 Update Action --->
    
    
    
    
    <!--- Step2 Update Action  --->
    <cffunction name="funcUpdateKronosHR" returntype="any">
        <cfargument default="-1" name="HRID" <!--- type="numeric" ---> required="yes" />
        <cfargument default="-1" name="EmpNo" <!--- type="numeric" ---> required="yes" />
        <cfargument default="" name="UserID" type="string"  required="no" />
        
        <!---<cfquery name="qryUpdateKronosHR" datasource="#Session.DataSource#">
             scf_SCFVM18_UpdateKronosHR #ARGUMENTS.HRID#, #ARGUMENTS.EmpNo#, '#ARGUMENTS.UserID#'    
            
        </cfquery>--->

        <cfreturn True />
    </cffunction>
    <!--- Step2 Update Action  --->
    
    
    
    <!--- Step2 CreateNonSCFMasterRecord --->
    <cffunction name="funcCreateNonSCFMasterRecord" returntype="any">
        <cfargument default="-1" name="EmployeeOBJ" />
        
        <cfquery name="qryCreateNonSCFMasterRecord" datasource="#Session.DatasourceRosetta#">
            scf_CreateNonSCFMasterRecord 
                '#replace(ARGUMENTS.EmployeeOBJ.First, " ", "")#',
                '#replace(ARGUMENTS.EmployeeOBJ.Last, " ", "")#',
                <cfif ARGUMENTS.EmployeeOBJ.Middle EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.Middle#'</cfif>,
                <cfif ARGUMENTS.EmployeeOBJ.Pref EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.Pref#'</cfif>,
                <cfif ARGUMENTS.EmployeeOBJ.SSN EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.SSN#'</cfif>,
                <cfif ARGUMENTS.EmployeeOBJ.Email EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.Email#'</cfif>,
                <cfif ARGUMENTS.EmployeeOBJ.WPhone EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.WPhone#'</cfif>,
                '#ARGUMENTS.EmployeeOBJ.Company#',
                '#ARGUMENTS.EmployeeOBJ.Dept#',
                '#ARGUMENTS.EmployeeOBJ.Supervisor#',
                <cfif ARGUMENTS.EmployeeOBJ.TypeID EQ ''>NULL<cfelse>'#ARGUMENTS.EmployeeOBJ.TypeID#'</cfif>            
        </cfquery>

        <cfreturn qryCreateNonSCFMasterRecord />
    </cffunction>
    <!--- Step2 CreateNonSCFMasterRecord  --->
    
    
    
    <!--- INSERT Rosetta Record--->
    <cffunction name="funcCreateRosettaRecordNonSCF" returntype="any">
        <cfargument default="" name="thread"  required="yes"/>  
        
        <cfquery name="qryCreateRosettaRecord" datasource="#Session.DataSourceRosetta#">           
            Insert into RosettaStone (
                UserAccount, AD_GUID, HRProd_PersonIDNo, EPAR_ID, NonSCFMasterID, [Disabled]
            )
            Values (
                <cfif ARGUMENTS.thread.UserID NEQ ''><cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.thread.UserID#" /><cfelse>NULL</cfif>, 
                <cfif ARGUMENTS.thread.AD_GUID NEQ ''><cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.thread.AD_GUID#" /><cfelse>NULL</cfif>, 
                <cfif ARGUMENTS.thread.HRProdID NEQ 0 OR ARGUMENTS.thread.HRProdID NEQ ''><cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.thread.HRProdID#" /><cfelse>NULL</cfif>, 
                <cfif ARGUMENTS.thread.EPAR_ID NEQ 0 OR ARGUMENTS.thread.EPAR_ID NEQ ''><cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.thread.EPAR_ID#" /><cfelse>NULL</cfif>,
                <cfif ARGUMENTS.thread.NonSCFMasterID NEQ 0 OR ARGUMENTS.thread.NonSCFMasterID NEQ ''><cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.thread.NonSCFMasterID#" /><cfelse>NULL</cfif>, 
                1
            )
            
            SELECT @@Identity AS NewRosettaID
        </cfquery>
        
        <cfreturn qryCreateRosettaRecord />
    </cffunction>
    <!--- INSERT Rosetta Record--->
    
    
	      
</cfcomponent>